/*****************************************************************************
 *   Copyright 2011 ZHUANG Yuan              	                             *
 *   SudokuITS as Final Year Project for BScCS, City University of Hong Kong *
 *                                      									 *
 *   Licensed under the Apache License, Version 2.0 (the "License");         *
 *   you may not use this file except in compliance with the License.        *
 *   You may obtain a copy of the License at                                 *
 *                                                                           *
 *   http://www.apache.org/licenses/LICENSE-2.0                              *
 *                                                                           *
 *   Unless required by applicable law or agreed to in writing, software     *
 *   distributed under the License is distributed on an "AS IS" BASIS,       *
 *   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.*
 *   See the License for the specific language governing permissions and     *
 *   limitations under the License.                                          *
 *****************************************************************************/

package android.sudoku.its.mysql;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;

import android.sudoku.its.sqlite.StudentTable;
import android.sudoku.its.student.Student;
import android.util.Log;

/**
 * Create MySQL database, which is an external database to store multiple user data 
 * @author 51158282
 *
 */
public class MySQLHandler {	
	private HttpPost httpPost;
	
	public MySQLHandler() {
		httpPost = getHttpPost();
	}
	
	public void syncDB(Student student) {
		String name = student.getName();
		String result = getStudentByName(name);
		if (result.contains("null")) { // such student does not exist in MySQL database
			// Insert new student
			insertNewStudent(student);
		}
		else {
			// Update existing student
			updateStudentById(student);
		}
		
	}
	
	public HttpPost getHttpPost() {
		String url = "http://144.214.121.32/queryStudent.php";
		httpPost = new HttpPost();
		try{	        
	        httpPost = new HttpPost(url);
		} catch (Exception e) {
			Log.e("Sudoku ITS DB", "Error in HTTP connection" + e.toString());
		}
		
		return httpPost;
	}
	
	private String convertResponseToString(InputStream is) {
		String result = "";
		//Convert response to string
		try{
	        BufferedReader reader = new BufferedReader(new InputStreamReader(is, "iso-8859-1"));
	        StringBuilder sb = new StringBuilder();
	        String line;
	        while ((line = reader.readLine()) != null) 
	        	sb.append(line + "\n");
	        
	        reader.close();	
	        result = sb.toString();
	        Log.d("Sudoku ITS DB", Boolean.toString(result.contains("null")));
	        Log.d("Sudoku ITS DB", result);
		}catch(Exception e){
			Log.e("Sudoku ITS DB", "Error converting result " + e.toString());
			}
		return result;
	}
	
	private String getStudentByName (String name) { 		
		final String OPERATION = "select";
		ArrayList<NameValuePair> studentValuePair = new ArrayList<NameValuePair>();		
		studentValuePair.add(new BasicNameValuePair("operation", OPERATION));
		studentValuePair.add(new BasicNameValuePair("name", name));
		String result = "";
		
		//HTTP post
		try{
			HttpClient httpClient = new DefaultHttpClient();
	        httpPost.setEntity(new UrlEncodedFormEntity(studentValuePair));
	        HttpResponse response = httpClient.execute(httpPost);
	        HttpEntity entity = response.getEntity();
	        result = convertResponseToString(entity.getContent());
	        }catch(Exception e){
		        Log.e("Sudoku ITS DB", "Error in HTTP connection " + e.toString());
		        }	
		
		return result;
	}
	
	private void updateStudentById(Student student) {
		String result = "";
		final String OPERATION = "update";
		ArrayList<NameValuePair> studentValuePair = new ArrayList<NameValuePair>();		
		studentValuePair.add(new BasicNameValuePair("operation", OPERATION));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_NAME, student.getName()));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_SUDOKU_EXPERIENCE, Integer.toString(student.getSudokuExperience())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_LEVEL, Integer.toString(student.getLevel())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_FULL_HOUSE, Integer.toString(student.getMasteryOnFullHouse())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_NAKED_SINGLE, Integer.toString(student.getMasteryOnNakedSingle())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_HIDDEN_SINGLE, Integer.toString(student.getMasteryOnHiddenSingle())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_NAKED_PAIR, Integer.toString(student.getMasteryOnNakedPair())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_HIDDEN_PAIR, Integer.toString(student.getMasteryOnHiddenPair())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_LOCKED_CANDIDATES, Integer.toString(student.getMasteryOnLockedCandidates())));

		//HTTP post
		try{
			HttpClient httpClient = new DefaultHttpClient();
	        httpPost.setEntity(new UrlEncodedFormEntity(studentValuePair));
	        HttpResponse response = httpClient.execute(httpPost);
	        HttpEntity entity = response.getEntity();
	        result = convertResponseToString(entity.getContent());
	        Log.d("Sudoku ITS DB", result);
	        }catch(Exception e){
		        Log.e("Sudoku ITS DB", "Error in HTTP connection " + e.toString());
		        }					
	}
	
	private void insertNewStudent(Student student) {
		String result = "";
		final String OPERATION = "insert"; 
		ArrayList<NameValuePair> studentValuePair = new ArrayList<NameValuePair>();		
		studentValuePair.add(new BasicNameValuePair("operation", OPERATION));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_NAME, student.getName()));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_PASSWORD, student.getPassword()));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_GENDER, student.getGender()));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_AGE, Integer.toString(student.getAge())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_SUDOKU_EXPERIENCE, Integer.toString(student.getSudokuExperience())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_LEVEL, Integer.toString(student.getLevel())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_EDUCATION_LEVEL, student.getEducationLevel()));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_SUBJECT, student.getSubject()));		
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_FULL_HOUSE, Integer.toString(student.getMasteryOnFullHouse())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_NAKED_SINGLE, Integer.toString(student.getMasteryOnNakedSingle())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_HIDDEN_SINGLE, Integer.toString(student.getMasteryOnHiddenSingle())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_NAKED_PAIR, Integer.toString(student.getMasteryOnNakedPair())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_HIDDEN_PAIR, Integer.toString(student.getMasteryOnHiddenPair())));
		studentValuePair.add(new BasicNameValuePair(StudentTable.COLUMN_MASTERY_ON_LOCKED_CANDIDATES, Integer.toString(student.getMasteryOnLockedCandidates())));

		//HTTP post
		try{
			HttpClient httpClient = new DefaultHttpClient();
	        httpPost.setEntity(new UrlEncodedFormEntity(studentValuePair));
	        HttpResponse response = httpClient.execute(httpPost);
	        HttpEntity entity = response.getEntity();
	        result = convertResponseToString(entity.getContent());
	        Log.d("Sudoku ITS DB", result);
	        }catch(Exception e){
		        Log.e("Sudoku ITS DB", "Error in HTTP connection " + e.toString());
		        }			
	}
}
